import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.NPOIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import uk.ac.shef.wit.simmetrics.similaritymetrics.AbstractStringMetric;
import uk.ac.shef.wit.simmetrics.similaritymetrics.JaroWinkler;

public class Bacsgep {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		String sapNev = null;
		String sapCikkszam = null;
		
		try {
			NPOIFSFileSystem sapFs = new NPOIFSFileSystem(new File("sap.xls"));
			HSSFWorkbook sapWb = new HSSFWorkbook(sapFs.getRoot(), true);
			HSSFSheet sapSheet = sapWb.getSheet("Lista_ Cikkek  sap");

//			NPOIFSFileSystem ibexFs = new NPOIFSFileSystem(new File("product.xlsx"));
//			HSSFWorkbook ibexWb = new HSSFWorkbook(ibexFs.getRoot(), true);
			XSSFWorkbook ibexWb = new XSSFWorkbook(new FileInputStream(new File("product.xlsx")));
			XSSFSheet ibexSheet = ibexWb.getSheet("product");

			for (int i = 1; i < sapSheet.getLastRowNum(); i++) {
				Row row = sapSheet.getRow(i);

				sapCikkszam = row.getCell(2).getStringCellValue();
				sapNev = row.getCell(3).getStringCellValue().trim();

				boolean result = false;
				if(sapCikkszam != null) {
					for (int j = 1; j < ibexSheet.getLastRowNum(); j++) {
						Row row2 = ibexSheet.getRow(j);
						String ibexNev = row2.getCell(8).getStringCellValue().trim();
	
						AbstractStringMetric metric = new JaroWinkler();
						float _result = metric.getSimilarity(sapNev, ibexNev);
						if (_result > 0.98) {					
						//if (sapNev.equalsIgnoreCase(ibexNev)) {
							row2.createCell(4).setCellValue(sapCikkszam);
							int id = (int)row2.getCell(0).getNumericCellValue();
	
							System.out.println(ibexNev + "\t\t\t" + sapNev);
	//System.out.println("update product set identifier = '" + sapCikkszam + "' where id = " + id + "    " + _result);						
							result = true;
							break;
						}
	
					}
	
					if (!result) {
						System.out.println(sapCikkszam + "\t\t\t" + sapNev);
					}
				}

			}

			FileOutputStream fileOut = new FileOutputStream("ibex2.xls");
			ibexWb.write(fileOut);
			fileOut.close();

		} catch (Exception e) {
			System.out.println("error: " + sapCikkszam);
			e.printStackTrace();
		}
	}

}
